current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 5 down vote favorite

In trying to create a simple PHP PDO update function that if the field is not found would insert it, I created this little snippet.

function updateorcreate($table,$name,$value){
    global $sodb;
    $pro = $sodb->prepare("UPDATE `$table` SET value = :value WHERE field = :name");
    if(!$pro){
        $pro = $sodb->prepare("INSERT INTO `$table` (field,value) VALUES (:name,:value)");
    }
    $pro->execute(array(':name'=>$name,':value'=>$value));
}

It does not detect though if the update function is going to work with if(!$pro); How would we make this one work.

share|improve this question
2  
+1 for using PDO. –  Harshal Mahajan Sep 5 '12 at 14:29
1  
I;m still getting used to it but am not going back to mySQL –  Robin Knight Sep 5 '12 at 14:29
1  
-1 for using global –  PeeHaa Sep 5 '12 at 14:29
1  
You first need to execute() it before you can tell whether the update caused rows to get updated using numRows(), but if the field already exists with the same value you might get unintended results as well. –  Ja͢ck Sep 5 '12 at 14:30
1  
@Lex but he only executes his first query at the bottom, so the if-condition is useless. –  Ja͢ck Sep 5 '12 at 14:32

4 Answers 4

up vote 10 down vote accepted

You are assigning $pro to the prepare, not the execute statement.

Having said that, if you are using mysql you can use the insert... on duplicate key update syntax.

insert into $table (field, value) values (:name, :value) on duplicate key update value=:value2

You can't use the same bound param twice, but you can set two bound params to the same value.

Edit: This mysql syntax will only work where a key (primary or another unique) is present and would cause an insert to fail.

share|improve this answer
    
I am using mySQL but this one is causing an error –  Robin Knight Sep 5 '12 at 14:41
    
This is what I tried: function updateorcreate($table,$name,$value){ global $sodb; $pro = $sodb->prepare("INSERT INTO $table (field, value) values (:name, :value) ON DUPLICATE KEY update value = :value2"); $pro->execute(array(':name'=>$name,':value'=>$value,':value2'=>$value)); } –  Robin Knight Sep 5 '12 at 14:42
    
@RobinKnight Sorry, I should have mentioned it right off the bat, you do have a unique constraint set on the table for the column field yes? –  Fluffeh Sep 5 '12 at 14:45
    
No perhaps I ought to as there should be no reason they would ever repeat –  Robin Knight Sep 5 '12 at 14:46
    
@RobinKnight Sorry about that mate, yes, the syntax will only work if the column is constrained. Apologies, should have mentioned that right from the start :( –  Fluffeh Sep 5 '12 at 14:48
up vote 2 down vote

If it's mysql-only you could try INSERT INTO ... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

share|improve this answer
up vote 0 down vote

You will first need to execute it.

Apart from that, this is a dodgy way of doing this. It would be better to start a transaction, do a SELECT and then determine what to do (INSERT or UPDATE). Just checking whether the UPDATE query succeeded doesn't suffice, it succeeds when no row is found too.

share|improve this answer
    
OK, I was trying to see if we could bypass that step but if its not safe then I won't –  Robin Knight Sep 5 '12 at 14:31
    
No, it's not safe. –  Sherlock Sep 5 '12 at 14:32
    
SELECT is a rather dodgy way of doing it too. Better would be to have a unique key on the field, and then use INSERT ... ON DUPLICATE KEY UPDATE .... –  cHao Sep 5 '12 at 14:32
    
I never read he wants to avoid duplication altogether. He just doesn't want it in this specific situation. –  Sherlock Sep 5 '12 at 14:33
up vote 0 down vote

try,

    PDO::exec()

returns 1 if inserted. 2 if the row has been updated.

for prepared statements,

    PDOStatement::execute()

You can try,

    PDOStement::rowCount()
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.